package com.vimi8.ebb.auth.service;

import com.vimi8.ebb.auth.dto.AuthUserDto;
import com.vimi8.ebb.auth.dto.OrgMemberTotalDTO;
import com.vimi8.ebb.auth.dto.UserDto;
import com.vimi8.ebb.auth.model.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by xuybin on 2016/9/9.
 */
@Mapper
public interface UserAuthorityService {

    @Select("SELECT count(*) FROM oauth_client_user t1 INNER JOIN oauth_user t2 " +
            " ON t1.user_id = t2.user_id WHERE t2.phone_number = #{phoneNumber} AND t1.client_id = #{clientId}")
    public int getResourceClientUserCountByPhoneNumber(@Param("phoneNumber") String phoneNumber, @Param("clientId") String clientId);

    @Update("update oauth_user set auth_code=#{authCode},auth_code_time=CURRENT_TIMESTAMP(), auth_code_is_used=0 where  phone_number=#{phoneNumber} ")
    public int updateUserDetailsForAuthCode(@Param("phoneNumber") String phoneNumber, @Param("authCode") String authCode);

    @Select("SELECT TIMESTAMPDIFF(MINUTE,auth_code_time,CURRENT_TIMESTAMP()) as a from oauth_user where phone_number=#{phoneNumber}")
    public Integer getAuthCodeTimestampDiff(String phoneNumber);

    @Select("select IFNULL(count(1),0) from oauth_user where phone_number = #{phoneNumber}")
    public int getUserPhoneNumberCount(@Param("phoneNumber") String phoneNumber);

    @Select("select IFNULL(count(1),0) from oauth_user where id_card = #{idCard}")
    public int getUseridCardCount(@Param("idCard") String idCard);



    @Insert(" INSERT  INTO `oauth_user`(`user_id`,`id_card`,`head_img`,`user_name`,`phone_number`,`password`,`auth_code_time`,`real_name`,`nick_name`,`address_code`,`address_detail`,`default_client_id`,`sex`)  " +
            " VALUES (#{userId},#{idCard},#{headImg},#{userName},#{phoneNumber},#{password},CURRENT_TIMESTAMP(),#{realName},#{nickName},#{addressCode},#{addressDetail},#{defaultClientId},#{sex})")
    public int insertOauthUser(RegUser user);

    @Insert("INSERT INTO `user_apply_client_resource`(`user_id`,`client_id`,`dept_id`) VALUES (#{userId}, #{clientId}, #{deptId})")
    public int insertUserApplyClient(@Param("userId") String userId, @Param("clientId") String clientId, @Param("deptId") String deptId);

    @Select("select `user_id` as userId,`client_id` as clientId,`dept_id` as deptId,resource_id as resourceId,scope as scope,job as job from user_apply_client_resource where client_id=#{clientId} and user_id=#{userId} and is_approval=0")
    public UserApplyClientResource queryUserApplyClient(@Param("userId") String userId, @Param("clientId") String clientId);

    @Select("SELECT client_id as clientId,dept_id as deptId,dept_parent_id as deptParentId ,dept_name as deptName,dept_resources as deptResources,dept_scopes as deptScopes,dept_place_detail as deptPlaceDetail,dept_place as deptPlace FROM `organization_dept` WHERE `client_id`=#{clientId} AND dept_id=#{deptId}")
    public OrganizationDept getOrganizationDept(@Param("clientId") String clientId, @Param("deptId") String deptId);

    //根据clientId查询用户信息
    @Select("SELECT u.`head_img` AS headImg,u.`real_name` AS realName,u.`phone_number` AS phoneNumber,u.`address_code` AS addressCode," +
            "u.`address_detail` AS addressDetail FROM oauth_user AS u WHERE u.`default_client_id`=#{clientId}")
    public AuthUser getAuthUserByClientId(String clientId);

    //根据clientId查询法人的openId
    @Select("SELECT distinct(o.org_corp) from organization o where o.client_id=#{clientId}")
    public String getCorpUserIdByClientId(String clientId);
    //修改用户信息
    @Update("update oauth_user set head_img=#{headImg},real_name=#{realName},phone_number=#{phoneNumber},address_code=#{addressCode}," +
            "address_detail=#{addressDetail} where default_client_id=#{defaultClientId}")
    public int updateAuthUser(AuthUser authUser);
    //修改站点信息
    @Update("update oauth_user set head_img=#{headImg},real_name=#{realName},address_code=#{addressCode}," +
            "address_detail=#{addressDetail} where default_client_id=#{defaultClientId}")
    public int updateSitUser(AuthUser authUser);
    //删除用户信息
    @Delete("delete from oauth_user where default_client_id=#{clientId}")
    public int deleteAuthUser(String clientId);
    //修改个人头像
    @Update("UPDATE oauth_user AS u,oauth_client_user AS c SET u.`head_img`=#{headImg} WHERE" +
            " c.`user_id` = u.`user_id` AND c.`open_id` =#{openId}")
    public int updateMyHeadImg(@Param("headImg") String headImg, @Param("openId") String openId);
    //修改个人姓名
    @Update("UPDATE oauth_user AS u,oauth_client_user AS c SET u.real_name=#{name} WHERE" +
            " c.`user_id` = u.`user_id` AND c.`open_id` =#{openId}")
    public int updateName(@Param("name") String name, @Param("openId") String openId);

    //修改身份证
    @Update("UPDATE oauth_user AS u,oauth_client_user AS c SET u.id_card=#{idCard},u.id_card_img=#{idCardImg}," +
            "u.is_auth=1 WHERE c.`user_id` = u.`user_id` AND c.`open_id` =#{openId}")
    public int updateMyIdCard(@Param("idCard") String idCard, @Param("idCardImg") String idCardImg, @Param("openId") String openId);

    //修改电话号码
    @Update("UPDATE oauth_user AS u,oauth_client_user AS c SET u.phone_number=#{phone} WHERE" +
            " c.`user_id` = u.`user_id` AND c.`open_id` =#{openId}")
    public int updatePhone(@Param("phone") String phone, @Param("openId") String openId);

    //修改地址信息
    @Update("UPDATE oauth_user AS u,oauth_client_user AS c SET u.address_code=#{addressCode}," +
            "u.address_detail=#{addressDetail} WHERE c.`user_id` = u.`user_id` AND c.`open_id` =#{openId}")
    public int updateAddress(@Param("addressCode") String addressCode,
                             @Param("addressDetail") String addressDetail,
                             @Param("openId") String openId);
    //根据openid查询手机号
    @Select("select u.`phone_number` from oauth_client_user as c join oauth_user as u on u.`user_id` = c.`user_id`" +
            " and c.`open_id` = #{openId}")
    public String getPhoneNumberByOpenId(@Param("openId") String openId);

    //根据openid查询用户信息
    @Select("SELECT c.open_id AS openId,u.`real_name` AS realName,u.`head_img` AS headImg FROM oauth_user AS u JOIN" +
            " oauth_client_user AS c ON c.user_id = u.`user_id` AND c.open_id=#{openId} limit 1")
    public UserDto getUserInfoByOpenId(@Param("openId") String openId);


    @Select("SELECT IFNULL(COUNT(1),0) FROM oauth_user AS u,oauth_client_user AS c WHERE c.`user_id` = u.`user_id` AND c.`open_id`=#{openId}" +
            " AND u.`password` =#{password};")
    public Integer selectPassword(@Param("password") String password, @Param("openId") String openId);


    /*
     * 添加组织部门员工信息
     * */
    @Insert(" INSERT  INTO `organization_emp`(`client_id`,`dept_id`,`user_id`,`job`,`create_time`)  " +
            " VALUES (#{clientId},#{deptId},#{userId},#{job},CURRENT_TIMESTAMP())")
    public int insertOrganizationEmp(OrganizationEmp organizationEmp);

    @Insert("INSERT INTO `user_apply_client_resource`(`user_id`,`client_id`,`resource_id`) VALUES (#{userId}, #{clientId}, #{resourceId})")
    public int insertUserApplyClientResource(@Param("userId") String userId, @Param("clientId") String clientId, @Param("resourceId") String resourceId);

    @Insert("INSERT INTO `user_apply_client_resource`(`user_id`,`client_id`,`scope`) VALUES (#{userId}, #{clientId}, #{scope})")
    public int insertUserApplyClientScope(@Param("userId") String userId, @Param("clientId") String clientId, @Param("scope") String scope);

    @Insert("INSERT  INTO `oauth_client_user`(`client_id`,`user_id`,`open_id`,`resources`,`scopes`)  " +
            " VALUES (#{clientId},#{userId},#{openId},#{resources},#{scopes})")
    public int insertOpenid(@Param("clientId") String clientId, @Param("userId") String userId, @Param("openId") String openId, @Param("resources") String resources, @Param("scopes") String scopes);
    @Insert("update `oauth_client_user` set client_id=#{clientId}, resources= #{resources}, scopes=#{scopes} where client_id='personal' and open_id=#{openId} " +
            "and user_id=#{userId}")
    public int updateOpenid(@Param("clientId") String clientId, @Param("userId") String userId, @Param("openId") String openId, @Param("resources") String resources, @Param("scopes") String scopes);

    @Select("select distinct(open_id) from `oauth_client_user` where user_id=#{userId} and client_id=#{clientId}")
    public String queryOpenidByUserIdAndClientId(@Param("userId") String userId, @Param("clientId") String clientId);
    @Select("select distinct(user_id) from `oauth_client_user` where open_id=#{openId}")
    public String queryUserIdByUserIdAndClientId(@Param("openId") String openId);

    //GROUP_CONCAT(t1.open_id) as openid
    @Select("SELECT (t1.open_id) as openid,t1.scopes as scopes,t1.resources as resources,t3.job AS job,o.org_class as clientOrgClass  FROM oauth_client_user t1 INNER JOIN organization o on o.client_id=t1.client_id INNER JOIN oauth_user t2 " +
            " ON t1.user_id = t2.user_id INNER JOIN organization_emp t3  ON t2.user_id = t3.user_id  WHERE" +
            " (t2.phone_number = #{loginName} OR t2.id_card = #{loginName} OR t2.user_name=#{loginName}) AND t1.client_id = #{clientId}  AND  t3.client_id =#{clientId}")
    public ClientUser getClientUserByLoginNameAndClientId(@Param("loginName") String loginName, @Param("clientId") String clientId);


    @Update("UPDATE  `oauth_user` SET `password`=#{newPassword} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE  client_id=#{clientId} AND open_id=#{openId})  AND `password`=#{oldPassword}")
    public int updatePassword(@Param("clientId") String clientId, @Param("openId") String openId, @Param("oldPassword") String oldPassword, @Param("newPassword") String newPassword);

    @Update("UPDATE  `oauth_user` SET `password`=#{newPassword} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE  client_id=#{clientId} AND open_id=#{openId})")
    public int updatePasswordByOpenId(@Param("clientId") String clientId, @Param("openId") String openId, @Param("newPassword") String newPassword);

    @Update("UPDATE  `oauth_user` SET `id_card`=#{idCard} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updateIdCard(@Param("clientId") String clientId, @Param("openId") String openId, @Param("idCard") String idCard);

    @Update("UPDATE  `oauth_user` SET `user_name`=#{userName} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updateUserName(@Param("clientId") String clientId, @Param("openId") String openId, @Param("userName") String userName);

    @Update("UPDATE  `oauth_user` SET `nick_name`=#{nickName} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updateNickName(@Param("clientId") String clientId, @Param("openId") String openId, @Param("nickName") String nickName);

    @Update("UPDATE  `oauth_user` SET `phone_number`=#{phoneNumber} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updatePhoneNumber(@Param("clientId") String clientId, @Param("openId") String openId, @Param("phoneNumber") String phoneNumber);

    @Update("UPDATE  `oauth_user` SET `real_name`=#{realName},address_code=#{addressCode},address_detail=#{addressDetail} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updateHeadImgAndAddress(@Param("realName") String realName, @Param("addressCode") String addressCode, @Param("addressDetail") String addressDetail, @Param("clientId") String clientId, @Param("openId") String openId);

    @Update("UPDATE  `oauth_user` SET `head_img`=#{headImg} WHERE `user_id` IN (SELECT user_id FROM `oauth_client_user` WHERE client_id=#{clientId} AND open_id=#{openId})")
    public int updateHeadImg(@Param("headImg") String headImg, @Param("clientId") String clientId, @Param("openId") String openId);

    @Update("UPDATE  `oauth_user` u,`oauth_client_user` ou SET u.`default_client_id`=#{defaultClientId} WHERE u.`user_id` =ou.`user_id` and ou.open_id=#{openId} and u.default_client_id!=#{defaultClientId}")
    public int updatDefaultClientId(@Param("defaultClientId") String defaultClientId, @Param("openId") String openId);

    @Select("SELECT COUNT(*) FROM `oauth_client_user` WHERE client_id=#{clientId} AND  open_id=#{openId}")
    public int getOpenIdCount(@Param("clientId") String clientId, @Param("openId") String openId);
    @Select("SELECT distinct(ou.user_id) FROM oauth_user ou,`oauth_client_user` ocu WHERE ocu.open_id=#{openId} and ou.user_id=ocu.user_id")
    public String  getUserIdByOpenId(@Param("openId") String openId);


    @Select("SELECT distinct(ocu.open_id) FROM oauth_user ou,`oauth_client_user` ocu WHERE ocu.user_id=#{userId} and ou.user_id=ocu.user_id limit 1")
    public String  getOpenIdIdByUserId(@Param("userId") String userId);

    @Update("UPDATE  `oauth_client_user` set scopes=#{scopes} WHERE client_id=#{clientId} AND  open_id=#{openId}")
    public int updateScopes(@Param("clientId") String clientId, @Param("openId") String openId, @Param("scopes") String scopes);

    @Update("UPDATE  `oauth_client_user` set scopes=#{scopes} WHERE client_id=#{clientId} AND  user_id=#{userId}")
    public int updateScopesByUserId(@Param("clientId") String clientId, @Param("userId") String userId, @Param("scopes") String scopes);

    @Select("UPDATE oauth_user SET default_client_id=#{clientId} where id=#{userId}")
    public int updateUserDefaultClientIdByUserId(@Param("userId") String userId, @Param("clientId") String clientId);

    @Select("<script> SELECT COUNT(1) FROM `oauth_client_user` ou,organization_emp oe WHERE ou.client_id=oe.client_id and ou.user_id=oe.user_id and oe.job=#{job} and " +
            " ou.client_id=#{clientId} AND  ou.open_id=#{openId} and 1=1 " +
            "   <if test=\"scopes!=null and scopes!=''\"> and ou.scopes like CONCAT('%',#{scopes},'%')</if> </script>")
    public int getOpenIdAndScopesCount(@Param("clientId") String clientId, @Param("openId") String openId, @Param("scopes") String scopes, @Param("job") String job);

    @Select("select scopes from oauth_client_user where client_id=#{clientId} and user_id=#{userId} limit 1")
    public String getScopesByUserId(@Param("clientId") String clientId, @Param("userId") String userId);


    //GROUP_CONCAT(t1.open_id) as openid
    @Select("SELECT t1.user_id as userId,t1.open_id as openid,t1.scopes as scopes,t1.resources as resources  FROM oauth_client_user t1 INNER JOIN oauth_user t2 " +
            " ON t1.user_id = t2.user_id WHERE t1.open_id=#{openId} and t1.client_id=#{clientId} limit 1")
    public ClientUser getClientUserByOpenid(@Param("clientId") String clientId, @Param("openId") String openId);

    @Delete("delete from `oauth_client_user` WHERE client_id=#{clientId} AND  open_id=#{openId}")
    public int deleteAuthorityByClientAndOpentId(@Param("clientId") String clientId, @Param("openId") String openId);

    @Select("SELECT DISTINCT(t1.user_id) as userId," +
            "id_card as idCard,user_name as userName,is_auth as isAuth,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM oauth_client_user t1 INNER JOIN " +
            "oauth_user t2 " +
            " ON t1.user_id = t2.user_id " +
            "WHERE t1.open_id=#{openId} and t1.client_id=#{clientId}")
    public AuthUserDto getAuthUserDtoByOpenid(@Param("openId") String openId, @Param("clientId") String clientId);

    @Select("SELECT DISTINCT(t1.user_id) as userId," +
            "id_card as idCard,user_name as userName,is_auth as isAuth,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg,`password` as password," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM oauth_client_user t1 INNER JOIN " +
            "oauth_user t2 " +
            " ON t1.user_id = t2.user_id " +
            "WHERE t1.open_id=#{openId} and t1.client_id=#{clientId}")
    public AuthUserDto getAuthUserDtoByOpenid1(@Param("openId") String openId, @Param("clientId") String clientId);

    @Select("SELECT DISTINCT(t2.user_id) as userId," +
            "id_card as idCard,user_name as userName,is_auth as isAuth,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg,`password` as password," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM " +
            "oauth_user t2 " +
            "WHERE t2.user_id=#{userId}")
    public AuthUserDto getAuthUserDtoByUserId(@Param("userId") String userId);

    @Select("SELECT distinct(ou.client_id) as clientId,o.org_name as orgName,o.org_class as orgClass,o.org_images as orgImages,oc.org_class_type as orgClassType,oc.org_class_name AS orgClassName," +
            " oe.job AS job  from oauth_client_user ou,organization o,organization_class oc ,organization_emp oe WHERE  oe.client_id=ou.client_id AND  oe.user_id=ou.`user_id` and " +
            "ou.client_id=o.client_id and is_approval=1 and o.org_class=oc.org_class and ou.user_id in(select user_id from oauth_client_user where open_id=#{openId})")
    public List<UserOrg> getUserOrgByOpenid(@Param("openId") String openId);

    @Select("SELECT distinct(ou.client_id) as clientId,o.org_name as orgName,o.org_class as orgClass,o.org_images as orgImages,oc.org_class_type as orgClassType,oc.org_class_name AS orgClassName," +
            " oe.job AS job  from oauth_client_user ou,organization o,organization_class oc ,organization_emp oe WHERE  oe.client_id=ou.client_id AND  oe.user_id=ou.`user_id` and " +
            "ou.client_id=o.client_id and o.org_class=oc.org_class and ou.user_id =#{userId}")
    public List<UserOrg> getUserOrgByUserId(@Param("userId") String userId);

    //关注组织
    @Select("SELECT o.`client_id` AS clientId,o.`org_name` AS orgName,o.`org_class` AS orgClass,o.`org_images` AS orgImages," +
            "c.`org_class_name` AS orgClassName,c.`org_class_type` AS orgClassType FROM user_org_relation AS u JOIN organization AS o ON o.`client_id` = u.`client_id` JOIN organization_class AS c ON \n" +
            "c.`org_class` = o.`org_class` WHERE u.`relation_type` = 1 AND u.`user_id` = #{openId}")
    public List<UserOrg> getFollowUserOrgByOpenId(@Param("openId") String openId);

    @Select("SELECT distinct(ou.client_id) as clientId,o.org_name as orgName,o.org_class as orgClass,o.org_images as orgImages,oc.org_class_type as orgClassType,oc.org_class_name AS orgClassName," +
            " oe.job AS job  from oauth_client_user ou,organization o,organization_class oc ,organization_emp oe WHERE  oe.client_id=ou.client_id AND  oe.user_id=ou.`user_id` and " +
            "ou.client_id=o.client_id and is_approval=1 and o.org_class=oc.org_class and ou.user_id=#{userId} limit 1")
    public UserOrg getUserOrgByUserid(@Param("userId") String userId);

    @Select("SELECT (t2.user_id) as userId " +
        " from oauth_user t2 where t2.user_id in " +
        "(select user_id from oauth_client_user t1 WHERE t1.user_id=t2.user_id and t1.open_id=#{openId} and t1.client_id=#{clientId})")
    public AuthUserDto getAuthUserDtoByOpenId1(@Param("openId") String openId, @Param("clientId") String clientId);


    @Select("SELECT count(DISTINCT(oe.user_id)) "+
            "  FROM organization_emp oe  " +
            "WHERE oe.client_id=#{clientId}")
    public int getEmpAuthUserCount(@Param("clientId") String clientId);

    @Select("<script> SELECT count(DISTINCT(u.user_id)) "+
            "  FROM oauth_user u,oauth_client_user ou,organization_emp oe " +
            "WHERE u.user_id=ou.user_id and u.is_auth=1 and ou.user_id=oe.user_id and u.user_id not in " +
            "(select oe1.user_id from organization_emp oe1 where oe1.client_id=#{clientId} " +
            "  and (oe1.job='org_emp' or oe1.job='org_shareholder_emp' or oe1.job='org_manager' or oe1.job='org_shareholder_manager'  or oe1.job='org_senior_manager' or oe1.job='org_corp') )  and 1=1" +
            "  <if test=\"isShakePoverty!=null and isShakePoverty!=''\"> and u.is_shake_poverty = #{isShakePoverty}</if> "+

            "  <if test=\" key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " </script>")
    public int getCountNotEmpAuthUser(@Param("clientId") String clientId, @Param("key") String key, @Param("isShakePoverty") String isShakePoverty);

    @Select("<script> SELECT DISTINCT(ou.user_id) as userId,ou.client_id as clientId,id_card as idCard,user_name as userName,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,address_code as addressCode,address_detail as addressDetail,u.is_shake_poverty as isShakePoverty,oe.is_freeze as isFreeze,oe.job as job " +
            "  FROM oauth_user u,oauth_client_user ou,organization_emp oe   " +
            "WHERE u.user_id=ou.user_id and u.is_auth=1 and ou.user_id=oe.user_id and u.user_id not in " +
            "(select oe1.user_id from organization_emp oe1 where oe1.client_id=#{clientId} " +
            " and (oe1.job='org_emp' or oe1.job='org_shareholder_emp' or oe1.job='org_manager' or oe1.job='org_shareholder_manager'  or oe1.job='org_senior_manager' or oe1.job='org_corp') )  and 1=1 " +
            "  <if test=\"isShakePoverty!=null and isShakePoverty!=''\"> and u.is_shake_poverty = #{isShakePoverty}</if> "+

            "  <if test=\" key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            "  </script>")
    public List<AuthUserDto> getNotEmpAuthUser(@Param("clientId") String clientId, @Param("key") String key, @Param("isShakePoverty") String isShakePoverty, @Param("start") int start, @Param("size") int size);

    @Select("<script> SELECT count(DISTINCT(u.user_id)) "+
            "  FROM oauth_user u,oauth_client_user ou,organization_emp oe " +
            "WHERE u.user_id=ou.user_id and u.is_auth=1 and ou.user_id=oe.user_id and u.user_id not in " +
            "(select oe1.user_id from organization_emp oe1 where oe1.client_id=#{clientId} " +
            "  and (oe1.job like concat('%','org_shareholder','%') OR oe1.job='org_corp') ) " +
            "  <if test=\"isShakePoverty!=null and isShakePoverty!=''\"> and u.is_shake_poverty = #{isShakePoverty}</if> "+

            "  <if test=\" key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " </script>")
    public int getCountNotShareholderAuthUser(@Param("clientId") String clientId, @Param("key") String key, @Param("isShakePoverty") String isShakePoverty);
    @Select("<script> SELECT DISTINCT(ou.user_id) as userId,ou.client_id as clientId,id_card as idCard,user_name as userName,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,address_code as addressCode,address_detail as addressDetail,u.is_shake_poverty as isShakePoverty,oe.is_freeze as isFreeze,oe.job as job " +
            "  FROM oauth_user u,oauth_client_user ou,organization_emp oe   " +
            "WHERE u.user_id=ou.user_id and u.is_auth=1 and ou.user_id=oe.user_id and u.user_id not in " +
            "(select oe1.user_id from organization_emp oe1 where oe1.client_id=#{clientId} " +
            " and (oe1.job like concat('%','org_shareholder','%')  OR oe1.job='org_corp') ) " +
            "  <if test=\"isShakePoverty!=null and isShakePoverty!=''\"> and u.is_shake_poverty = #{isShakePoverty}</if> "+

            "  <if test=\" key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " </script>")
    public List<AuthUserDto> getNotShareholderAuthUser(@Param("clientId") String clientId, @Param("key") String key, @Param("isShakePoverty") String isShakePoverty, @Param("start") int start, @Param("size") int size);


    @Select("SELECT t1.user_id as userId,t1.open_id as openId,t1.scopes as scopes,o.org_class as clientOrgClass,t1.client_id as clientId," +
            "id_card as idCard,user_name as userName,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM oauth_client_user t1 INNER JOIN " +
            "oauth_user t2 " +
            " ON t1.user_id = t2.user_id inner join organization o on o.client_id=t1.client_id and t1.client_id=#{clientId}" +
            "WHERE t1.open_id=#{openId}")
    public AuthUserDto getAuthUserDtoByClientId(@Param("clientId") String clientId, @Param("openId") String openId);

    @Select("<script> SELECT COUNT(1) FROM `oauth_user` WHERE 1=1 " +
            "  <if test=\" geoCode!=null and geoCode!=''\"> and address_code like CONCAT('%',#{geoCode},'%')</if> " +
            "  <if test=\" placeGroup!=null and placeGroup!=''\"> and address_detail like CONCAT('%',#{placeGroup},'%')</if> " +
            " </script>")
    public int getAllPoorCount(@Param("geoCode") String geoCode, @Param("placeGroup") String placeGroup);
    @Update("update oauth_client_user t1, oauth_user t2 set last_login_date= CURRENT_TIMESTAMP()" +
         " where  t1.client_id=t2.default_client_id and t1.user_id = t2.user_id and t1.open_id = #{openId} AND t1.client_id = #{clientId}")
    public int updateLastLoginTime(@Param("clientId") String clientId, @Param("openId") String openId);

    //根据openid查询用户登录信息
    @Select("SELECT t1.user_id as userId,t1.open_id as openId,t1.scopes as scopes,t2.password as password,o.org_class as clientOrgClass,t1.client_id as clientId," +
            "id_card as idCard,user_name as userName,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM oauth_client_user t1 INNER JOIN " +
            "oauth_user t2 " +
            " ON t1.user_id = t2.user_id  inner join organization o on o.client_id=t1.client_id and t1.client_id=#{clientId}" +
            "WHERE t1.open_id=#{openId}")
    public AuthUserDto getLoginUserByClientIdAndOpenId(@Param("clientId") String clientId, @Param("openId") String openId);
    //根据openid查询用户登录信息
    @Select("SELECT t2.user_id as userId," +
            "id_card as idCard,user_name as userName,phone_number as phoneNumber,real_name as realName,nick_name as nickName,head_img as headImg," +
            "sex,t2.address_code as addressCode,t2.address_detail as addressDetail,t2.default_client_id as defaultClientId" +
            "  FROM  " +
            "oauth_user t2 " +
            "WHERE t2.user_id=#{userId}")
    public AuthUser getAuthUserByUserId(@Param("userId") String userId);

    //根据clientId查询贫困户数、贫困人口数
    @Select("SELECT count(ou.user_id) as poorHouseholdTotal,IFNULL(sum(ou.people_num),0) AS poorPersonsTotal from oauth_user ou,organization o WHERE ou.`default_client_id`=#{clientId}" +
            " and ou.is_shake_poverty=0 and o.client_id=ou.default_client_id and o.org_corp!=ou.user_id")
    public OrgMemberTotalDTO getOrgMemberTotal(String clientId);


    /**
     * 打卡
     * @param punchClock
     * @return
     */
    @Insert("insert into punch_clock(id,user_id,punch_address,longitude,latitude,punch_time) " +
            "values(#{id},#{userId},#{punchAddress},#{longitude},#{latitude},CURRENT_TIMESTAMP())")
    public int insertPunchClock(PunchClock punchClock);


    /**
     * 根据用户id查询打卡记录
     * @param openId
     * @return
     */
    @Select("select id,user_id as userId,punch_address as punchAddress,longitude,latitude,punch_time as punchTime " +
            "from punch_clock where user_id=#{openId} limit #{start},#{size}")
    public List<PunchClock>  getPunchTheClockInfo(@Param("openId") String openId, @Param("start") int start, @Param("size") int size);

    /**
     * 根据用户id查询打卡记录总记录数
     * @param openId
     * @return
     */
    @Select("select IFNULL(count(1),0) from punch_clock where user_id=#{openId}")
    public int  getPunchTheClockInfoCount(@Param("openId") String openId);

    /**
     * 查询本月打卡天数
     * @param openId
     * @return
     */
    @Select("SELECT IFNULL(count(1),0) FROM punch_clock WHERE DATE_FORMAT( punch_time, '%Y%m' ) =DATE_FORMAT( CURDATE( ) , '%Y%m' ) " +
            "and user_id = #{openId}")
    public int getToMouthPunchDayNumber(@Param("openId") String openId);

    /**
     * 查询今日是否已打卡
     * @param openId
     * @return
     */
    @Select("SELECT IFNULL(count(1),0) FROM punch_clock WHERE TO_DAYS(punch_time)=TO_DAYS(NOW()) and user_id=#{openId}")
    public int getTodayPunchClock(@Param("openId") String openId);

/**
 * 查询手机号是否存在*/
    @Select("SELECT ou.user_id as userId FROM `oauth_user` ou WHERE (ou.phone_number =#{phoneNumber} or ou.user_name =#{phoneNumber})")
    public String getUserId(@Param("phoneNumber") String phoneNumber);
}
